﻿CREATE PROCEDURE [dbo].[state_NewPTP]
	@NoteID int
AS

SET NOCOUNT ON

IF NOT EXISTS(SELECT * FROM dbo.t_Note WHERE ID = @NoteID AND StatusID = dbo.fs_StateID__Notes__PromisedToPay()) RETURN

DECLARE @DM int SET @DM =  dbo.Registry_PtpDueDaysMaximum()
DECLARE @NoteDate datetime,@DateDue datetime,@Days float
SELECT @NoteDate = dbo.clr_BusinessDaysAdd(NoteDate0,0),
			 @DateDue = dbo.clr_BusinessDaysAdd(DateDue,0),
			 @Days = dbo.clr_BusinessDaysDiff(dbo.clr_BusinessDaysAdd(NoteDate0,0), dbo.clr_BusinessDaysAdd(DateDue,0),1)
FROM dbo.t_Note
WHERE ID = @NoteID AND 
			NOT dbo.clr_BusinessDaysDiff(dbo.clr_BusinessDaysAdd(NoteDate0,0), dbo.clr_BusinessDaysAdd(DateDue,0),1) BETWEEN 0 AND @DM

IF NOT @NoteDate IS NULL
BEGIN
	EXEC s_Print 'NoteDate:{0:MM/dd/yy HH:mm},DateDue:{1:MM/dd/yy HH:mm},Days:{2:###.##}',@NoteDate,@DateDue,@Days
	DECLARE @S sysname SET @S = CONVERT(varchar,dbo.clr_BusinessDaysAdd(@NoteDate,0),101)+' and '+ CONVERT(varchar,dbo.clr_BusinessDaysAdd(@NoteDate,@DM),101)
	RAISERROR('Promise to pay date must be between %s.',16,1,@S)
	ROLLBACK
	RETURN
END

UPDATE N SET DateDue = dbo.clr_Date(dbo.clr_BusinessDaysAdd(N.DateDue,0))
FROM dbo.t_Note N
WHERE N.ID = @NoteID AND dbo.clr_Date(dbo.clr_BusinessDaysAdd(N.DateDue,0)) <> N.DateDue

--UPDATE N SET StatusID = dbo.fs_StateID__Notes__CancelledPTP()
IF EXISTS(SELECT *
FROM dbo.t_Note N INNER JOIN dbo.t_Note i
	ON i.ID = @NoteID
		 AND N.LoanID = i.LoanID
		 AND N.StatusID = dbo.fs_StateID__Notes__PromisedToPay()
		 AND dbo.clr_BusinessDaysDiff(dbo.clr_Date(i.NoteDate),dbo.clr_Date(N.DateDue),1) > 0
		 AND N.ID <> i.ID
)BEGIN
	ROLLBACK
	RAISERROR('This account already has pending PTP.',16,1)
	RETURN
END
		 --AND i.VantageID IS NULL

--BEGIN TRAN
--EXECUTE AS Login='AFC-HOME\SQLServer'
--exec sp_executesql 
--N'INSERT INTO [t_Note] ([LoanID], [Note], [StatusID], [DateDue], [DateDueBy], [NoteTypeID], [NoteActionCodeID], [Phone], [IsResolved]) VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9)'
--,N'@p1 int,@p2 nvarchar(4000),@p3 int,@p4 datetime,@p5 varchar(5),@p6 int,@p7 int,@p8 varchar(8000),@p9 bit'
--,@p1=1003672,@p2=N'',@p3=85,@p4='2009-11-17 09:00:00:000',@p5='MAlba',@p6=1,@p7=1,@p8=NULL,@p9=1
--exec sp_executesql 
--N'INSERT INTO [t_Note] ([LoanID], [Note], [StatusID],[NoteDate], [DateDue], [DateDueBy], [NoteTypeID], [NoteActionCodeID], [Phone], [IsResolved])
--	VALUES (@p1, @p2, @p3,@p10, @p4, @p5, @p6, @p7, @p8, @p9)'
--,N'@p1 int,@p2 nvarchar(4000),@p3 int,@p4 datetime,@p5 varchar(5),@p6 int,@p7 int,@p8 varchar(8000),@p9 bit,@p10 datetime'
--,@p1=1003672,@p2=N'',@p3=85,@p10='2009-11-17',@p4='2009-11-19 09:00:00:000',@p5='MAlba',@p6=1,@p7=1,@p8=NULL,@p9=1
--SELECT TOP 2 * FROM t_Note WHERE LoanID = 1003672 ORDER BY ID DESC
--SELECT * FROM t_State WHERE ID = 85
--REVERT
--ROLLBACK
--


